IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pGetProductByCriteria'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pGetProductByCriteria
GO

CREATE PROC dbo.pGetProductByCriteria
  @vintProductTypeId    INT = NULL,
  @vintProducerId       INT = NULL,
  @vstrProductName		NVARCHAR(255) = NULL,
  @vbitCheckNotSalePrice		BIT = 0,
  @vbitCheckNotListedPrice		BIT = 0,
  @vbitCheckNotWarrantyTime		BIT = 0,
  @vintAddBy					INT = NULL,
  @vdatAddFrom					DATETIME = NULL,
  @vdatAddTo					DATETIME = NULL,
  @vbitInputPriceChange		BIT = 0,
  @vintPriceDiffFrom		INT = NULL,
  @vintPriceDiffTo			INT = NULL
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

  DECLARE
    @cstrThisProc              NVARCHAR(128),
    @cintErrGeneral            INT

  SET @cstrThisProc            = N'pGetProductByCriteria'
  SET @cintErrGeneral          = 50000  -- General error. Used when an
                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/

	SELECT 
		Product.Id,
		Product.[Name],
		RowNumber = ROW_NUMBER() OVER(ORDER BY Product.[Name]),
		Product.ProducerId,
		Product.ProductTypeId,
		Product.SalePrice,
		Product.ListedPrice,
		Product.HasWarranty,
		Product.WarrantyTime,
		Product.WarningAmount,
		Product.Amount,
		Product.LatestInputPrice,
		SecondLatestInputPrice = SecondInProd.InputPrice,
		ProductTypeName = ProductType.[Name],
		ProducerName = Producer.[Name]
	FROM Product 
		
		INNER JOIN ProductType 
			ON Product.ProductTypeId = ProductType.Id
			
		INNER JOIN Producer 
			ON Product.ProducerId = Producer.Id
			
		LEFT JOIN vInOutProdDetail SecondInProd
			ON Product.Id = SecondInProd.ProductId
			AND SecondInProd.InputPrice IS NOT NULL
			AND SecondInProd.[Type] = 0
			AND (1) = (SELECT COUNT(DISTINCT(InProd2.[Date])) 
					FROM vInOutProdDetail InProd2
					WHERE InProd2.[Date] > SecondInProd.[Date]
					AND InProd2.ProductId = Product.Id)
			
	WHERE Product.IsDeleted = 0
		AND (@vintProductTypeId IS NULL OR Product.ProductTypeId = @vintProductTypeId)
		AND (@vintProducerId IS NULL OR Product.ProducerId = @vintProducerId)
		AND (@vstrProductName IS NULL OR Product.[Name] LIKE '%' + @vstrProductName + '%')
		AND ((@vbitCheckNotSalePrice = 0) OR (Product.SalePrice IS NULL OR Product.SalePrice = 0))
		AND ((@vbitCheckNotListedPrice = 0) OR (Product.ListedPrice IS NULL OR Product.ListedPrice = 0))
		AND ((@vbitCheckNotWarrantyTime = 0) OR (Product.HasWarranty = 1 AND (Product.WarrantyTime IS NULL OR Product.WarrantyTime = 0)))
		AND (@vintAddBy IS NULL OR Product.AddedBy = @vintAddBy)	
		AND (@vdatAddFrom IS NULL OR (DATEDIFF(DAY, @vdatAddFrom, Product.AddedDate) >= 0))
		AND (@vdatAddTo IS NULL OR (DATEDIFF(DAY, Product.AddedDate, @vdatAddTo) >= 0))
		AND ((@vbitInputPriceChange = 0) OR ((Product.LatestInputPrice > 0) AND (SecondInProd.InputPrice > 0) AND (Product.LatestInputPrice <> SecondInProd.InputPrice)))
		AND (@vintPriceDiffFrom IS NULL OR ((Product.SalePrice - Product.LatestInputPrice)/(Product.LatestInputPrice*1.0)*100 >= @vintPriceDiffFrom))
		AND (@vintPriceDiffTo IS NULL OR ((Product.SalePrice - Product.LatestInputPrice)/(Product.LatestInputPrice*1.0)*100 <= @vintPriceDiffTo)) 
		
	ORDER BY Product.[Name]

  -- Return value 0 indicates success
  SET @intReturn = 0

lblExit:

  RETURN @intReturn

/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


